/*
  FILENAME	: write_all.sql
  Copyright (C) 2013  Testmonger
  See https://inroad.codeplex.com/license

  	select * from FileDefinition
    select * from PersonTEMP
	select * from SupplierTEMP
	exec write_all
*/

IF OBJECT_ID(N'write_all') IS NOT NULL 
BEGIN
	DROP procedure write_all
END
GO

CREATE PROCEDURE write_all
AS
BEGIN
	DECLARE @outfile varchar(100), @vsql nvarchar(1000) , @vfileid bigint,
		@cnt1 int, @cnt2 int

	select @cnt1 = max(FileID), @cnt2 = min(fileid) from FileDefinition	

	WHILE @cnt2 <= @cnt1
	BEGIN
		select @outfile = rtrim(OutFilePath)+rtrim(OutFileName)+'.'+rtrim(OutFileExt)
		from FileDefinition where fileid = @cnt2

		select @vsql = 'bcp "'+dbo.ufnTableQryRTRIM(@cnt2)+'" queryout "'+rtrim(@outfile)+'" -c -T -t, -S '+@@SERVERNAME+' -d '+DB_NAME()

		--select @vsql = 'echo ' + @vsql + ' > x_' + @outfile + '.cmd'
	-- 	exec write_all
		
		insert into FileRunHistory values( getdate(), @cnt2, NULL, NULL )

		BEGIN
			EXEC master..xp_cmdshell @vsql , no_output 
		END

		select  @cnt2= min(fileid) from FileDefinition where fileid > @cnt2
	END

END

-- select * from FileRunHistory
-- exec write_all